package model;


import java.sql.*;
import java.util.Vector;

import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

import data.*;
import java.util.*;



public class StudentDBModel {

	public StudentDBModel(){}
	
	private static String connectString = "jdbc:postgresql://localhost/135DB?user=postgres&password=admin";
	
	private static String selectCountryById = "SELECT * FROM countries WHERE id=?";
	private static String selectSpecById = "SELECT * FROM specializations WHERE id=?";
	private static String selectUniById = "SELECT * FROM universities WHERE id=?";
	private static String selectDiscById = "SELECT * FROM disciplines WHERE id=?";
	private static String selectFeedbackByStudentId = "SELECT * FROM studentreviewerrelationships WHERE student_id=? AND graded=1";
	
	private static String selectAllCountries = "SELECT * FROM countries";
	private static String selectAllStates = "SELECT * FROM states";
	private static String selectUniversityByLocation = "SELECT * FROM universities WHERE location= ?";
	private static String addUniversity = "INSERT INTO universities (location, name) VALUES ( ?, ?)";
	private static String selectAllDisciplines = "SELECT * FROM disciplines";
	private static String queryDuplicateUniversity = "SELECT * FROM universities WHERE location=? AND name=?";
	private static String queryDuplicateDiscipline = "SELECT * FROM disciplines WHERE name=? ";
	private static String addMajor = "INSERT INTO disciplines (name) VALUES (?)";
	private static String selectAllSpecializations = "SELECT * FROM specializations";
	private static String updateUser = "UPDATE users SET appid=? WHERE user_name=?";
	private static String getUserApp = "SELECT appid FROM users WHERE user_name=?";
	private static String getStudentById = "SELECT * FROM students WHERE id=?";
	private static String getUser_ID= "SELECT user_id FROM users WHERE user_name=?";
	private static String insertDegree = "INSERT INTO degrees (university, discipline, student, date, gpa, type) VALUES " +
										 "(?, ?, ?, ?, ?, ?)";
	private static String insertApplicant = "INSERT INTO students (firstname, middleinitial, lastname, citizenship, "+
											"residence, street, city, zipcode, areacode, telephonecode, telephonenumber, " +
											"specialization, state, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '[PENDING]')";
	private static String reviewStarted = "SELECT reviewstarted FROM reviewstartedtable";
	private static String insertReviewer = "INSERT INTO users (user_name, password) VALUES (?, md5(?))";
	private static String insertReviewerRoles = "INSERT INTO user_roles (user_ref, user_name, role) VALUES(?,?,?)";
	private static String deleteReviewer = "DELETE from Users where user_name=?";
	private static String checkNumReviewers = "SELECT * FROM user_roles WHERE role=?";
	private static String getAppsByReviewer = "SELECT * FROM studentreviewerrelationships WHERE reviewer_name=?";
	private static String getStudentNames = "SELECT * FROM users WHERE user_id=?";
	private static String startStopReview = "UPDATE reviewstartedtable SET reviewstarted=? WHERE id=1";
	private static String assignReviewers = "INSERT INTO studentreviewerrelationships (student_id, reviewer_name, graded) VALUES (?,?,0) ";
	private static String getApps = "SELECT * from studentreviewerrelationships WHERE reviewer_name=?";
	
	private static String getStudentNamesByReviewer = "SELECT students.id, students.firstname, students.middleinitial, students.lastname FROM  public.studentreviewerrelationships,  public.students WHERE  students.id = studentreviewerrelationships.student_id AND  studentreviewerrelationships.reviewer_name = ? AND studentreviewerrelationships.graded=0";
	private static String getEntireAppByStudentID = "SELECT students.id, students.firstname, students.middleinitial, " + 
													"students.lastname, students.citizenship, students.residence, " + 
													"students.street, students.city, students.zipcode, students.areacode, " +
													"students.telephonecode, students.telephonenumber, students.specialization, " +
													"students.state, students.status, degrees.id, degrees.university, degrees.discipline," +
													" degrees.student, degrees.date, degrees.gpa, degrees.\"type\" FROM "+
													"public.students, public.degrees WHERE degrees.student = students.id AND "+
													"students.id = ?;";
	
	private static String submitFeedback= "UPDATE studentreviewerrelationships SET (graded, score, comment) = (1, ?, ?) WHERE reviewer_name=? AND student_id=?;";

	
	
	public String addApplication(StudentData studentData, String user)
	{
		String appID = null;
		
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);
			
			conn.setAutoCommit(false);

			Statement stmt = conn.createStatement();
			ResultSet rset;
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(insertApplicant);
			pStmt.setString(1, studentData.getFirstName());
			pStmt.setString(2, studentData.getMiddleInitial());
			pStmt.setString(3, studentData.getLastName());
			pStmt.setInt(4, Integer.valueOf(studentData.getCitizenID()));
			pStmt.setInt(5, Integer.valueOf(studentData.getResidentID()));
			pStmt.setString(6, studentData.getStreet());
			pStmt.setString(7, studentData.getCity());
			pStmt.setString(8, studentData.getZip());
			pStmt.setString(9, studentData.getAreaCode());
			pStmt.setString(10, studentData.getCountryCode());
			pStmt.setString(11, studentData.getPhone());
			pStmt.setInt(12, Integer.valueOf(studentData.getSpecializationID()));
			pStmt.setString(13, studentData.getState());
			
			
			System.err.println(pStmt);
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			pStmt.executeUpdate();
			
			rset = stmt.executeQuery("SELECT last_value FROM students_id_seq");
			
			while(rset.next())
			{
				appID = rset.getString("last_value");
			}
			
			
			Vector<Degree> degreeList = studentData.getDegrees();
			for(int i = 0; i < degreeList.size(); i++)
			{
				Degree degree = degreeList.get(i);
				pStmt = conn.prepareStatement(insertDegree);
				pStmt.setInt(1, Integer.valueOf(degree.getUniversityID()));
				pStmt.setInt(2, Integer.valueOf(degree.getMajorID()));
				pStmt.setInt(3, Integer.valueOf(appID));
				pStmt.setString(4, degree.getDate());
				pStmt.setString(5, degree.getGpa());
				pStmt.setString(6, degree.getType());
				System.err.println(pStmt);
				pStmt.executeUpdate();				
			}
			
			pStmt = conn.prepareStatement(updateUser);
			pStmt.setInt(1, Integer.valueOf(appID));
			pStmt.setString(2, user);
			
			pStmt.executeUpdate();
			
			conn.commit();
			
			stmt.close();
			pStmt.close();
			conn.close();

			return appID;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
		
	}
	
	public Vector<String> getApp(String user)
	{
		Vector<String> retVec = null;
		
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(getUserApp);
			pStmt.setString(1, user);
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet rset = pStmt.executeQuery();
			int appID = 0;
			if(rset.next())
			{
				appID = rset.getInt("appID");
			}
			else
			{
				return retVec;
			}
			
			
			pStmt = conn.prepareStatement(getStudentById);
			pStmt.setInt(1, appID);
			
			rset = pStmt.executeQuery();
			if(rset.next())
			{
				retVec = new Vector<String>();
				retVec.add("Application for:");
				retVec.add(rset.getString("firstname") +" "+ rset.getString("middleinitial") +" "+ rset.getString("lastname"));
				retVec.add("Application Status: " + rset.getString("status"));
			}
			else
			{
				return retVec;
			}
			

			rset.close();
			pStmt.close();
			conn.close();

			return retVec;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
	}
	
	
	public String majorExists( String name)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			String retVal = null;
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(queryDuplicateDiscipline);
			pStmt.setString(1, name);
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet disciplines = pStmt.executeQuery();

			if(disciplines.next())
			{
				retVal = disciplines.getString("id");
			}
			

			disciplines.close();
			pStmt.close();
			conn.close();

			return retVal;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
	}
	
	public String addMajor( String name )
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			Statement stmt = conn.createStatement();
			ResultSet rset;
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(addMajor);
			pStmt.setString(1, name);
			
			
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			pStmt.executeUpdate();
			
			rset = stmt.executeQuery("SELECT last_value FROM disciplines_id_seq");
			
			String retVal = "";
			while(rset.next())
			{
				retVal = rset.getString("last_value");
			}
			stmt.close();
			pStmt.close();
			conn.close();

			return retVal;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
	}
	
	public String universityExists( String name, String location)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			String retVal = null;
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(queryDuplicateUniversity);
			pStmt.setString(1, location);
			pStmt.setString(2, name);
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet university = pStmt.executeQuery();

			if(university.next())
			{
				retVal = university.getString("id");
			}
			

			university.close();
			pStmt.close();
			conn.close();

			return retVal;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
		
	}
	
	
	public static CachedRowSet selectAllSpecializations()
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectAllSpecializations);
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allSpecs = pStmt.executeQuery();

			CachedRowSet crsDisc = new CachedRowSetImpl();
			crsDisc.populate(allSpecs);

			allSpecs.close();
			pStmt.close();
			conn.close();

			return crsDisc;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
	}
	
	public static CachedRowSet selectAllDisciplines()
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectAllDisciplines);
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allDisc = pStmt.executeQuery();

			CachedRowSet crsDisc = new CachedRowSetImpl();
			crsDisc.populate(allDisc);

			allDisc.close();
			pStmt.close();
			conn.close();

			return crsDisc;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
	}
	
	
	public CachedRowSet getUniversitiesByLocation(String location)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectUniversityByLocation);
			pStmt.setString(1, location);
			
			
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allCountries = pStmt.executeQuery();

			CachedRowSet crsCountries = new CachedRowSetImpl();
			crsCountries.populate(allCountries);

			allCountries.close();
			pStmt.close();
			conn.close();

			return crsCountries;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
	}
	
	public String addUniversity(String name, String location)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			Statement stmt = conn.createStatement();
			ResultSet rset;
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(addUniversity);
			pStmt.setString(1, location);
			pStmt.setString(2, name);
			
			
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			pStmt.executeUpdate();
			
			rset = stmt.executeQuery("SELECT last_value FROM universities_id_seq");
			
			String retVal = "";
			while(rset.next())
			{
				retVal = rset.getString("last_value");
			}
			stmt.close();
			pStmt.close();
			conn.close();

			return retVal;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
		
	}
	
	
	public static CachedRowSet getAllCountries()  {

		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectAllCountries);
			
			
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allCountries = pStmt.executeQuery();

			CachedRowSet crsCountries = new CachedRowSetImpl();
			crsCountries.populate(allCountries);

			allCountries.close();
			pStmt.close();
			conn.close();

			return crsCountries;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
	}
	
	
	public static CachedRowSet getAllStates() {
		
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectAllStates);
			
			
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allStates = pStmt.executeQuery();

			CachedRowSet crsStates = new CachedRowSetImpl();
			crsStates.populate(allStates);

			allStates.close();
			pStmt.close();
			conn.close();

			return crsStates;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
		
	}
	
	public static boolean getReviewStarted(){
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(reviewStarted);
			ResultSet rset = pStmt.executeQuery();

		
			
			if(rset.next())
			{
				int val = rset.getInt("reviewstarted");
				if(val == 1)
					return true;
			}
			else{
				return false;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} 
		return false;
	}
	
	public boolean addReviewers(String name, String pword){
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);
			
			// Insert into the users table
			PreparedStatement pStmt = conn.prepareStatement(insertReviewer);
			pStmt.setString(1, name);
			pStmt.setString(2, pword);
			pStmt.executeUpdate();
			
			//get the user's id from the users table
			PreparedStatement pStmt2 = conn.prepareStatement(getUser_ID);
			pStmt2.setString(1, name);
			ResultSet rset = pStmt2.executeQuery();
			int user_id = -1;
			while(rset.next())
			{
				user_id = rset.getInt("user_id");
			}
			
			

			// Insert into the user_roles Table
			PreparedStatement pStmt3 = conn.prepareStatement(insertReviewerRoles);			
			pStmt3.setInt(1, user_id);
			pStmt3.setString(2, name);
			pStmt3.setString(3, "reviewer");
			pStmt3.executeUpdate();
			
			
			pStmt.close();
			conn.close();
			
			return true;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} 

	}
	
	public boolean deleteReviewers(String name, String pword){
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(deleteReviewer);
			pStmt.setString(1, name);
			ResultSet rset = pStmt.executeQuery();

			pStmt.close();
			conn.close();
			
			return true;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} 

	}
	
	public static boolean canWeStartReviewing(){
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);
			
			
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(checkNumReviewers);
			pStmt.setString(1, "reviewer");
			ResultSet rset = pStmt.executeQuery();

			int i = 0;
			while(rset.next()){
				
				i++;
				if(i >= 2){
					return true;
				}
			}
			
			return false;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} 

	}
	
	public String getCountryNameById(int countryID)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectCountryById);
			pStmt.setInt(1, countryID);
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet country = pStmt.executeQuery();
			String countryName = "";

			while (country.next())
			{
				countryName = country.getString("country");
			}
			
			pStmt.close();
			conn.close();

			return countryName;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
		
	}
	
	public String getSpecNameById(int specID)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectSpecById);
			pStmt.setInt(1, specID);
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet spec = pStmt.executeQuery();
			String specName = "";

			while (spec.next())
			{
				specName = spec.getString("specialization");
			}
			
			pStmt.close();
			conn.close();

			return specName;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
	}
	
	public String getUniNameById(int univID)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectUniById);
			pStmt.setInt(1, univID);
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet univ = pStmt.executeQuery();
			String uniName = "";

			while (univ.next())
			{
				uniName = univ.getString("name");
			}
			
			pStmt.close();
			conn.close();

			return uniName;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
	}
	
	public String getDiscNameById(int discID)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectDiscById);
			pStmt.setInt(1, discID);
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet disc = pStmt.executeQuery();
			String discName = "";

			while (disc.next())
			{
				discName = disc.getString("name");
			}
			
			pStmt.close();
			conn.close();

			return discName;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
	}
	
	public CachedRowSet getEntireAppByStudentID(String studentID)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(getEntireAppByStudentID);
			pStmt.setInt(1, Integer.valueOf(studentID));
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet app = pStmt.executeQuery();

			CachedRowSet crsApp = new CachedRowSetImpl();
			crsApp.populate(app);
			
			pStmt.close();
			conn.close();

			return crsApp;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
		
	}
	
	public void assignReviewer(String reviewer, String studentID)
	{
		
		String insert = "INSERT INTO studentreviewerrelationships (student_id, reviewer_name, graded) VALUES (?,?,0)";
		
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(insert);
			pStmt.setInt(1, Integer.valueOf(studentID));
			pStmt.setString(2, reviewer);
			
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			pStmt.executeUpdate();

			
			
			pStmt.close();
			conn.close();

			
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} 
	}
	
	public void admitApplicant (String studentID)
	{
		 String admit = "UPDATE students SET status='ADMITTED' WHERE id=?";
		 
		 try {
				
				Class.forName("org.postgresql.Driver");
				
				Connection conn = DriverManager.getConnection(connectString);

				
				// Create the statement
				PreparedStatement pStmt = conn.prepareStatement(admit);
				pStmt.setInt(1, Integer.valueOf(studentID));
				
				
				
				
				// Use the created statement to SELECT
				// the student attributes FROM the Student table.
				pStmt.executeUpdate();
				
				

				pStmt.close();
				conn.close();


			} catch (Exception ex) {
				ex.printStackTrace();

			} 
		
	}
	
	public void rejectApplicant (String studentID)
	{
		 String admit = "UPDATE students SET status='REJECTED' WHERE id=?";
		 
		 try {
				
				Class.forName("org.postgresql.Driver");
				
				Connection conn = DriverManager.getConnection(connectString);

				
				// Create the statement
				PreparedStatement pStmt = conn.prepareStatement(admit);
				pStmt.setInt(1, Integer.valueOf(studentID));
				
				
				
				
				// Use the created statement to SELECT
				// the student attributes FROM the Student table.
				pStmt.executeUpdate();
				
				

				pStmt.close();
				conn.close();


			} catch (Exception ex) {
				ex.printStackTrace();

			} 
		
	}
	
	public Vector<String> getUnassignedReviewers(String studentID)
	{
		String andString = " AND user_name!=?";
		String selectFromUserRoles = "SELECT * FROM user_roles WHERE role='reviewer'";
		String selectReviewersForStudent = "SELECT reviewer_name FROM studentreviewerrelationships where student_id=?";
		
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectReviewersForStudent);
			pStmt.setInt(1, Integer.valueOf(studentID));
			ResultSet rset = pStmt.executeQuery();
			
			Vector<String> alreadyReviewed = new Vector<String>();
			while(rset.next())
			{
				alreadyReviewed.add(rset.getString("reviewer_name"));
			}
			
			String query = selectFromUserRoles;
			for(int i = 0; i < alreadyReviewed.size(); i++)
			{
				query = query + andString;
			}
			
			pStmt = conn.prepareStatement(query);
			for(int i = 0; i < alreadyReviewed.size(); i++)
			{
				pStmt.setString(i+1, alreadyReviewed.get(i));
			}
			
			
			
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet otherReviewers = pStmt.executeQuery();
			
			Vector<String> remainingReviewers = new Vector<String>();
			
			while(otherReviewers.next())
			{
				
				remainingReviewers.add(otherReviewers.getString("user_name"));
			}
			
			pStmt.close();
			conn.close();

			return remainingReviewers;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
		
		
	}
	
	public int submitFeedback(String reviewer, String studentID, String score, String comment)
	{
		
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(submitFeedback);
			pStmt.setInt(1, Integer.valueOf(score));
			pStmt.setString(2, comment);
			pStmt.setString(3, reviewer);
			pStmt.setInt(4, Integer.valueOf(studentID));
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			pStmt.executeUpdate();
			
			pStmt = conn.prepareStatement("SELECT count(*) FROM studentreviewerrelationships WHERE student_id=? AND graded=1");
			pStmt.setInt(1, Integer.valueOf(studentID));
			
			ResultSet feedbackCount = pStmt.executeQuery();
			int count = -1;

			if(feedbackCount.next())
			{
				count = feedbackCount.getInt("count");
			}
			
			pStmt.close();
			conn.close();

			return count;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return (-1);
		} 
		
		
	}
	
	public CachedRowSet getAppFeedback(String studentID)
	{
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectFeedbackByStudentId);
			pStmt.setInt(1, Integer.valueOf(studentID));
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet feedback = pStmt.executeQuery();

			CachedRowSet crsFeedback = new CachedRowSetImpl();
			crsFeedback.populate(feedback);
			
			pStmt.close();
			conn.close();

			return crsFeedback;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
		
	}
	
	public CachedRowSet getStudentNamesByReviewer(String reviewerName){
		
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(getStudentNamesByReviewer);
			pStmt.setString(1, reviewerName);
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet students = pStmt.executeQuery();

			CachedRowSet crsStudents = new CachedRowSetImpl();
			crsStudents.populate(students);
			
			pStmt.close();
			conn.close();

			return crsStudents;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		} 
		
	}
	
	public static boolean startReviewProcess(){
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(startStopReview);
			pStmt.setInt(1, 1);
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			pStmt.executeUpdate();
			
			return true;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}
		
	}

	public static boolean stopReviewProcess(){
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(startStopReview);
			pStmt.setInt(1, 0);
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			pStmt.executeUpdate();
			
			return true;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}
		
	}
	
	public static void assignReviewers(){
		try {
			
			int student_id;
			Vector<String> reviewers = new Vector<String>();
			Iterator<String> itr;			
			
			
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM user_roles WHERE role=?");
			pStmt.setString(1, "reviewer");
			ResultSet rSet = pStmt.executeQuery();
			
			while(rSet.next()){
				reviewers.add( rSet.getString("user_name") );
			}
	
			PreparedStatement pStmt2 = conn.prepareStatement("SELECT * FROM users where appid IS NOT NULL");
			ResultSet rSet2 = pStmt2.executeQuery();
			
			for(int i=0; rSet2.next(); i++){
				student_id = rSet2.getInt("appid");
				PreparedStatement pStmt3 = conn.prepareStatement(assignReviewers); 
				pStmt3.setInt(1, student_id);
				pStmt3.setString(2, reviewers.elementAt(i%reviewers.size()) );
				pStmt3.executeUpdate();			
			}
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
			
		}
		
	}

	public static Vector<String> getReviewers(){
		try {
			
			Vector<String> reviewers = new Vector<String>();		
			
			
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt2 = conn.prepareStatement("SELECT * FROM user_roles WHERE role=?");
			pStmt2.setString(1, "reviewer");
			ResultSet rSet2 = pStmt2.executeQuery();
			
				while(rSet2.next()){
					reviewers.add( rSet2.getString("user_name") );
				}

			return (Vector<String>)reviewers;

			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
		
	}
	
	
	//This is where the Application object is instantiated and passed back up to Chairs_3_GradedVNon.java
	public Vector<Application> getGradedApps(String reviewer){
		
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM studentreviewerrelationships WHERE reviewer_name=?");
			pStmt.setString(1, reviewer);
			ResultSet rSet = pStmt.executeQuery();	
			
					
			Vector<Application> ret = new Vector<Application>();
			Application app;
			
			int student_id;
			String student_name;
			while(rSet.next()){
				
				if(rSet.getInt("graded") == 1){
					student_id = rSet.getInt("student_id");
					PreparedStatement pStmt2 = conn.prepareStatement("SELECT * FROM students WHERE id=?");
					pStmt2.setInt(1, student_id);
					ResultSet rSet2 = pStmt2.executeQuery();
					if(rSet2.next()){
						student_name = rSet2.getString("firstname")+" "+rSet2.getString("middleinitial")+" "+rSet2.getString("lastname"); 
					}
					else{
						student_name = "failed to retrieve the student's name";
					}
					app = new Application( student_name, student_id, rSet.getInt("score"), rSet2.getString("status") );	
					ret.add(app);
				}
			}

			return ret;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
		
	}
	
	public Vector<Application> getUnGradedApps(String reviewer){
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM studentreviewerrelationships WHERE reviewer_name=?");
			pStmt.setString(1, reviewer);
			ResultSet rSet = pStmt.executeQuery();			
					
			Vector<Application> ret = new Vector<Application>();
			Application app;
			
			int student_id;
			String student_name;
			while(rSet.next()){
				
				if(rSet.getInt("graded") == 0){
					student_id = rSet.getInt("student_id");
					PreparedStatement pStmt2 = conn.prepareStatement("SELECT * FROM students WHERE id=?");
					pStmt2.setInt(1, student_id);
					ResultSet rSet2 = pStmt2.executeQuery();
					if(rSet2.next()){
						student_name = rSet2.getString("firstname")+" "+rSet2.getString("middleinitial")+" "+rSet2.getString("lastname"); 
					}
					else{
						student_name = "failed to retrieve the student's name";
					}
					app = new Application( student_name, student_id, rSet.getInt("score"), rSet2.getString("status") );	
					ret.add(app);
				}
			}

			return ret;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}		
	}
	
	public Vector<String> getAppNames( String reviewer){
		
		Vector<String> names = new Vector<String>();
		
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement(getApps);
			pStmt.setString(1, reviewer);
			ResultSet rSet = pStmt.executeQuery();

			PreparedStatement pStmt2;
			ResultSet rSet2;
			
			while(rSet.next()){
				pStmt2 = conn.prepareStatement("SELECT * FROM students where id=?");
				pStmt2.setInt(1, rSet.getInt("student_id"));
				rSet2 = pStmt2.executeQuery();
				while(rSet2.next()){
					names.add( rSet2.getString("firstname") + rSet2.getString("middleinitial") + rSet2.getString("lastname") );
				}
			}

			return names;

			
		} catch (Exception ex) {
			ex.printStackTrace();			
			return null;
		}
	}
		
		public Vector<String> getAppStatuses( String reviewer){
			
			Vector<String> statuses = new Vector<String>();
			
			try {
				Class.forName("org.postgresql.Driver");
				Connection conn = DriverManager.getConnection(connectString);
				PreparedStatement pStmt = conn.prepareStatement(getApps);
				pStmt.setString(1, reviewer);
				ResultSet rSet = pStmt.executeQuery();

				PreparedStatement pStmt2;
				ResultSet rSet2;
				
				while(rSet.next()){
					pStmt2 = conn.prepareStatement("SELECT * FROM students where id=?");
					pStmt2.setInt(1, rSet.getInt("student_id"));
					rSet2 = pStmt2.executeQuery();
					while(rSet2.next()){
						statuses.add( rSet2.getString("status") );
					}
				}

				return statuses;

				
			} catch (Exception ex) {
				ex.printStackTrace();
				return null;
			}
			
	}
		
		
	public Vector<Integer> getAppGrades( String reviewer){
			
			Vector<Integer> grades = new Vector<Integer>();
			
			try {
				Class.forName("org.postgresql.Driver");
				Connection conn = DriverManager.getConnection(connectString);
				PreparedStatement pStmt = conn.prepareStatement(getApps);
				pStmt.setString(1, reviewer);
				ResultSet rSet = pStmt.executeQuery();

				PreparedStatement pStmt2;
				ResultSet rSet2;
				int avg = 0;
				
				while(rSet.next()){
			
						avg += rSet.getInt("score");
					
					grades.add(avg);
				}

				return grades;

				
			} catch (Exception ex) {
				ex.printStackTrace();
				return null;
			}
			
	}
	
	public static Vector<String> discAnalyticsFull(){
		Vector<String> ret = new Vector<String>();
		String discName = "";
		String discID = "";
		String discCount = "";
		ResultSet discRset;
		ResultSet degreeRset;
		
		
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM disciplines;");
			Statement stmt2 = conn.createStatement();
			discRset = pStmt.executeQuery();

			while(discRset.next())
			{
				discName = discRset.getString("name");
				discID = discRset.getString("id");
				
				degreeRset = stmt2.executeQuery("SELECT count(*) FROM degrees WHERE discipline=" + discID );
				while( degreeRset.next() )
				{
					discCount = degreeRset.getString("count");
					ret.add(discName + " discID = " + discID + " discCount = " );
				}
			}

			return ret;

			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}	
	}

	public static Vector<Integer> discAnalyticsNum(){
		Vector<Integer> ret = new Vector<Integer>();
		String discName = "";
		String discID = "";
		Integer discCount;
		ResultSet discRset;
		ResultSet degreeRset;
		
		
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM disciplines;");
			Statement stmt2 = conn.createStatement();
			discRset = pStmt.executeQuery();

			while(discRset.next())
			{
				discName = discRset.getString("name");
				discID = discRset.getString("id");
				
				degreeRset = stmt2.executeQuery("SELECT count(*) FROM degrees WHERE discipline=" + discID );
				while( degreeRset.next() )
				{
					discCount = (Integer)degreeRset.getInt("count");
					ret.add( discCount );
				}
			}

			return ret;

			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
				
	}
	
	public Vector<String> getAppsInDisc(String discNum){
		Vector<String> ret = new Vector<String>();
		ResultSet rSet;
		int disc=0;
		int university;
		String name;
		String date;
		String gpa;
		String type;
		PreparedStatement pStmt2;
		ResultSet rSet2;
		
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM degrees WHERE discipline=?");
			
			if(discNum.equals("1") ){
				disc = 1;
			}
			else if(discNum.equals("2") ){
				disc = 2;
			}
			else if(discNum.equals("3") ){
				disc = 3;
			}
			else if(discNum.equals("4") ){
				disc = 4;
			}
			
			pStmt.setInt(1, disc);
			rSet = pStmt.executeQuery();

			while(rSet.next())
			{
				pStmt2 = conn.prepareStatement("SELECT * FROM students WHERE id=?");
				pStmt2.setInt(1, rSet.getInt("student") );
				rSet2 = pStmt2.executeQuery();
				rSet2.next();
				name = rSet2.getString("firstname")+" "+rSet2.getString("middleinitial")+" "+rSet2.getString("lastname");				
				date = rSet.getString("date");
				gpa = rSet.getString("gpa");
				type = rSet.getString("type");
				ret.add(name+" -- Graduation Date: "+date+" GPA: "+gpa+" Degree: "+type);
			}

			return ret;

			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
		
		
	}

	public static Vector<String> specAnalyticsFull(){
		Vector<String> ret = new Vector<String>();
		String specName = "";
		String specID = "";
		String specCount = "";
		ResultSet specRset;
		ResultSet degreeRset;
		
		
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM specializations;");
			Statement stmt2 = conn.createStatement();
			specRset = pStmt.executeQuery();

			while(specRset.next())
			{
				specName = specRset.getString("specialization");
				specID = specRset.getString("id");
				
				degreeRset = stmt2.executeQuery("SELECT count(*) FROM students WHERE specialization=" + specID );
				while( degreeRset.next() )
				{
					specCount = degreeRset.getString("count");
					ret.add(specName + " discID = " + specID + " discCount = " );
				}
			}

			return ret;

			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}	
	}

	public static Vector<Integer> specAnalyticsNum(){
		Vector<Integer> ret = new Vector<Integer>();
		String specName = "";
		String specID = "";
		Integer specCount;
		ResultSet discRset;
		ResultSet degreeRset;
		
		
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM specializations;");
			Statement stmt2 = conn.createStatement();
			discRset = pStmt.executeQuery();

			while(discRset.next())
			{
				specName = discRset.getString("specialization");
				specID = discRset.getString("id");
				
				degreeRset = stmt2.executeQuery("SELECT count(*) FROM students WHERE specialization=" + specID );
				while( degreeRset.next() )
				{
					specCount = (Integer)degreeRset.getInt("count");
					ret.add( specCount );
				}
			}

			return ret;

			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
				
	}
	
	public Vector<String> getAppsInSpec(String specNum){
		Vector<String> ret = new Vector<String>();
		ResultSet rSet;
		int spec=0;
		int university;
		String name;
		String date;
		String gpa;
		String type;
	
		
		try {
			Class.forName("org.postgresql.Driver");
			Connection conn = DriverManager.getConnection(connectString);
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM students WHERE specialization=?");
			
			if(specNum.equals("1") ){
				spec = 1;
			}
			else if(specNum.equals("2") ){
				spec = 2;
			}
			else if(specNum.equals("3") ){
				spec = 3;
			}
			else if(specNum.equals("4") ){
				spec = 4;
			}
			else if(specNum.equals("5") ){
				spec = 5;
			}
			else if(specNum.equals("6") ){
				spec = 6;
			}
			else if(specNum.equals("7") ){
				spec = 7;
			}
			else if(specNum.equals("8") ){
				spec = 8;
			}
			else if(specNum.equals("9") ){
				spec = 9;
			}
			else if(specNum.equals("10") ){
				spec = 10;
			}
			else if(specNum.equals("11") ){
				spec = 11;
			}
			else if(specNum.equals("12") ){
				spec = 12;
			}
			else if(specNum.equals("13") ){
				spec = 13;
			}
			else if(specNum.equals("14") ){
				spec = 14;
			}
			else if(specNum.equals("15") ){
				spec = 15;
			}
			else if(specNum.equals("16") ){
				spec = 16;
			}
			else if(specNum.equals("17") ){
				spec = 17;
			}
			else if(specNum.equals("18") ){
				spec = 18;
			}
			else if(specNum.equals("19") ){
				spec = 19;
			}
			else if(specNum.equals("20") ){
				spec = 20;
			}
			else if(specNum.equals("21") ){
				spec = 21;
			}
			pStmt.setInt(1, spec);
			rSet = pStmt.executeQuery();

			while(rSet.next())
			{
				name = rSet.getString("firstname")+" "+rSet.getString("middleinitial")+" "+rSet .getString("lastname");				
				date = rSet.getString("street");
				gpa = rSet.getString("city");
				type = rSet.getString("zipcode");
				ret.add(name+" -- Address: "+date+" "+gpa+" "+type);
			}

			return ret;
			
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
		
		
	}
	
	public void updateStatus(String reviewer, String status){
		try {
			
			Class.forName("org.postgresql.Driver");
			
			Connection conn = DriverManager.getConnection(connectString);
			String mangledSpec = reviewer;
			String[] tokens = new String[10];
			tokens = mangledSpec.split(" ");
			String name = tokens[0];
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement("UPDATE students SET status=? WHERE firstname=?");
			pStmt.setString(1, status);
			pStmt.setString(2, name);
			
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			pStmt.executeUpdate();
			
			
			
		} catch (Exception ex) {
			ex.printStackTrace();
			
		}
		
	}
	
	public Vector<String> getAjaxInfo(String student){
		try {
			Vector<String> ret = new Vector<String>();
			int student_id=1;
			
			String mangledSpec = student;
			String[] tokens = new String[10];
			tokens = mangledSpec.split(" ");
			String name = tokens[0];
			
			Class.forName("org.postgresql.Driver");			
			Connection conn = DriverManager.getConnection(connectString);
			// Create the statement
			
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM students WHERE firstname=?");
			pStmt.setString(1, student);			
			ResultSet rSet = pStmt.executeQuery();
			
			PreparedStatement pStmt2;
			ResultSet rSet2;
			while(rSet.next()){			
				pStmt2 = conn.prepareStatement("SELECT * FROM countries WHERE id=?");
				pStmt2.setInt(1, rSet.getInt("citizenship") );
				rSet2 = pStmt2.executeQuery();
				if(rSet2.next()	){
					ret.add( "citizenship: "+rSet2.getString("country"));
				}
				
				ret.add( "street: "+rSet.getString("street") );
				ret.add( "city: "+rSet.getString("city") );
				ret.add( "zipcode: "+rSet.getString("zipcode") );
				ret.add( "areacode: "+rSet.getString("areacode") );
				student_id = rSet.getInt("id");
			}
			
			pStmt = conn.prepareStatement("SELECT * FROM studentreviewerrelationships WHERE student_id=?");
			pStmt.setInt(1, student_id);			
			rSet = pStmt.executeQuery();
			while(rSet.next()){
				ret.add("score: "+rSet.getInt("score"));
				ret.add("comments: "+rSet.getString("comment"));
			}
			
			
			return ret;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
	}
	
	public Vector<String> getApplication(int id){
		try {
			Vector<String> ret = new Vector<String>();
			int student_id = id;
			
			Class.forName("org.postgresql.Driver");			
			Connection conn = DriverManager.getConnection(connectString);
			// Create the statement
			
			PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM students WHERE id=?");
			pStmt.setInt(1, student_id);			
			ResultSet rSet = pStmt.executeQuery();
			
			PreparedStatement pStmt2;
			ResultSet rSet2;
			while(rSet.next()){			
				pStmt2 = conn.prepareStatement("SELECT * FROM countries WHERE id=?");
				pStmt2.setInt(1, rSet.getInt("citizenship") );
				rSet2 = pStmt2.executeQuery();
				if(rSet2.next()	){
					ret.add( "citizenship: "+rSet2.getString("country"));
				}

				ret.add( "street: "+rSet.getString("street") );
				ret.add( "city: "+rSet.getString("city") );
				ret.add( "zipcode: "+rSet.getString("zipcode") );
				ret.add( "areacode: "+rSet.getString("areacode") );
			}
			
			pStmt = conn.prepareStatement("SELECT * FROM studentreviewerrelationships WHERE student_id=?");
			pStmt.setInt(1, student_id);			
			rSet = pStmt.executeQuery();
			while(rSet.next()){
				ret.add("score: "+rSet.getInt("score"));
				ret.add("comments: "+rSet.getString("comment"));
			}
			
			
			return ret;
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}

	}
	
}
